using AutoMapper;
using Npoi.Mapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using Publicuse.Entity;
using Spire.Xls;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Mapper = Npoi.Mapper.Mapper;

namespace Publicuse.Util
{
    /// <summary>
    /// Excel工具类
    /// </summary>
    public class ExcelUtil
    {
        private static ExcelUtil excelUtil = null;

        static ExcelUtil()
        {
            excelUtil = new ExcelUtil();
        }

        private ExcelUtil()
        {

        }

        public static ExcelUtil GetExcelUtil
        {
            get { return excelUtil; }
        }

    /// <summary>
    /// Excel转为List
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="fileStream"></param>
    /// <param name="func"></param>
    /// <param name="firstrowindex"></param>
    /// <param name="sheetname"></param>
    /// mapper.Map<Student>("性别", "Sex", (c, t) => {
    ///   Student student = t as Student;
    ///   student.Sex = c.CurrentValue == "男" ? "MAN" : "WOMAN";
    ///   return true;
    /// }, null);
    /// <returns></returns>
    public static IEnumerable<T> ParseExcelToList<T>(
        Stream fileStream,
        Func<Mapper, Mapper> func,
        int firstrowindex = -1,
        string sheetname = "Sheet1"
    )
        where T : class
    {
      //通过上传文件流初始化Mapper
      var mapper = new Mapper(fileStream);
      if (func != null)
      {
        mapper = func.Invoke(mapper);
      }
      mapper.FirstRowIndex = firstrowindex;
      IEnumerable<T> ModelList = null;
      //读取sheet1的数据
      ModelList = mapper.Take<T>(sheetname).Select(i => i.Value);
      if (ModelList != null && ModelList.Count() == 0)
      {
        ModelList = mapper.Take<T>(0).Select(i => i.Value);
      }
      return ModelList;
    }

    public static IEnumerable<dynamic> ParseExcelToList(
        Stream fileStream,
        Func<Mapper, Mapper> func,
        int firstrowindex = -1,
        string sheetname = "Sheet1"
    )
    {
      //通过上传文件流初始化Mapper
      var mapper = new Mapper(fileStream);
      if (func != null)
      {
        mapper = func.Invoke(mapper);
      }
      mapper.FirstRowIndex = firstrowindex;
      IEnumerable<dynamic> ModelList = null;
      //读取sheet1的数据
      ModelList = mapper.Take<dynamic>(sheetname).Select(i => i.Value);
      if (ModelList != null && ModelList.Count() == 0)
      {
        ModelList = mapper.Take<dynamic>(0).Select(i => i.Value);
      }
      return ModelList;
    }

    /// <summary>
    /// List转Excel
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list">数据</param>
    /// <param name="sheetName">表名</param>
    /// <param name="overwrite">true,覆盖单元格，false追加内容(list和创建的excel或excel模板)</param>
    /// <param name="xlsx">true-xlsx，false-xls</param>
    ///  var fileStream = ExcelUtil.ParseListToExcel(usrs);
    ///  return File(fileStream.ToArray(), "application/vnd.ms-excel", "用户信息.xlsx");
    ///   List<Student> students = new List<Student>
    /// {
    ///new Student{ Id = 1,Name="夫子",Sex="男",BirthDay=new DateTime(1999,10,11) },
    ///new Student{ Id = 2,Name="余帘",Sex="女",BirthDay=new DateTime(1999,12,12) },
    /// new Student { Id = 3, Name = "李慢慢", Sex = "男", BirthDay = new DateTime(1999, 11, 11) },
    ///new Student { Id = 4, Name = "叶红鱼", Sex = "女", BirthDay = new DateTime(1999, 10, 10) }
    /// };
    ///  var mapper = new Mapper();
    //第一个参数表示导出的列名，第二个表示对应的属性字段
    ///mapper.Map<Student>("姓名", s => s.Name)
    ///.Map<Student>("学号", s => s.Id)
    ///.Map<Student>("性别", s => s.Sex)
    ///.Map<Student>("生日", s => s.BirthDay)
    /// 格式化操作，第一个参数表示格式，第二表示对应字段
    /// Format不仅仅只支持时间操作，还可以是数字或金额等
    ///.Format<Student>("yyyy-MM-dd", s => s.BirthDay);
    ///mapper.Save("Students.xlsx", students, "sheet1", overwrite: true, xlsx:true);
    /// <returns>返回文件</returns>
    public static MemoryStream ParseListToExcel<T>(
        List<T> list,
        string sheetName = "sheet1",
        bool overwrite = true,
        bool xlsx = true
    )
        where T : class
    {
      var mapper = new Mapper();
      MemoryStream ms = new MemoryStream();
      mapper.Save<T>(ms, list, sheetName, overwrite, xlsx);
      return ms;
    }

    /// <param name="columnName">对应Excel列的名称</param>
    /// <param name="propertyName">对应实体的属性名称</param>
    /// <param name="tryTake">该函数用于处理从Excel读取时针对单元格数据的处理</param>
    /// <param name="tryPut">该函数用于处理将数据导出到Excel是针对源数据的处理</param>
    public static Mapper NopiMap<T>(
        Mapper mapper,
        string columnName,
        string propertyName,
        Func<IColumnInfo, object, bool> tryTake = null,
        Func<IColumnInfo, object, bool> tryPut = null
    )
    {
      return mapper;
    }

    public void ExcelToImg(string path, string name)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(path);
            //将第一张工作表保存为图片                                                                
            Worksheet sheet = workbook.Worksheets[0];
            sheet.SaveToImage(name);
        }

        /// <summary>
        /// 生成excel表格模板
        /// </summary>
        /// <param name="module">模块名称</param>
        /// <param name="generators">导出列名称集合</param>
        /// <param name="filePath">文件保存路径</param>
        /// <param name="msg"></param>
        public void InitExcel(string module, List<Generator> generators, string filePath, out string msg)
        {
            msg = string.Empty;
            IWorkbook workbook = new HSSFWorkbook();
            var borderstyle = BorderStyle.Medium;
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.BorderBottom = borderstyle;
            style.BorderLeft = borderstyle;
            style.BorderRight = borderstyle;
            style.BorderTop = borderstyle;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 13;
            font.Boldweight = (short)FontBoldWeight.Bold;
            style.SetFont(font);

            ICellStyle style2 = workbook.CreateCellStyle();
            style2.Alignment = HorizontalAlignment.Right;
            style2.BorderBottom = borderstyle;
            style2.BorderLeft = borderstyle;
            style2.BorderRight = borderstyle;
            style2.BorderTop = borderstyle;
            style2.SetFont(font);
            //创建工作表 
            ISheet sheetx = workbook.CreateSheet(module);
            IRow rowx0 = sheetx.CreateRow(0);
            IRow rowx1 = sheetx.CreateRow(1);
            rowx0.CreateCell(0).SetCellValue(module);
            rowx0.GetCell(0).CellStyle = style;

            rowx1.CreateCell(0).SetCellValue("日期：");
            rowx1.GetCell(0).CellStyle = style2;

            var index = 0;
            IRow rowx2 = sheetx.CreateRow(2);
            var exportlist = generators.Where(o => o.istitle == true).OrderBy(o => o.titleindex);
            foreach (var item in exportlist)
            {
                rowx2.CreateCell(index).SetCellValue(item.name.Split('-')[0]);
                rowx2.GetCell(index).CellStyle = style;
                sheetx.SetColumnWidth(index, 20 * 256);
                index++;
            }
            if (exportlist.Any(o => o.isexportdefault == true) && exportlist.Count(o => o.exporttemplate == "") == 0)
            {
                rowx2.CreateCell(index).SetCellValue("创建时间");
                rowx2.GetCell(index).CellStyle = style;
                sheetx.SetColumnWidth(index, 20 * 256);
                index++;
                rowx2.CreateCell(index).SetCellValue("更新时间");
                rowx2.GetCell(index).CellStyle = style;
                sheetx.SetColumnWidth(index, 20 * 256);
                index++;
                rowx2.CreateCell(index).SetCellValue("描述");
                rowx2.GetCell(index).CellStyle = style;
                sheetx.SetColumnWidth(index, 20 * 256);
                index++;
                rowx2.CreateCell(index).SetCellValue("状态");
                rowx2.GetCell(index).CellStyle = style;
                sheetx.SetColumnWidth(index, 20 * 256);
                index++;
            }
            if (generators.Count == 1)
            {
                workbook.GetSheetAt(0).AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));//起始行，结束行，起始列，结束列
                workbook.GetSheetAt(0).AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));//起始行，结束行，起始列，结束列
            }
            else
            {
                if (exportlist.Count() <= 4)
                {
                    workbook.GetSheetAt(0).AddMergedRegion(new CellRangeAddress(0, 0, 0, exportlist.Count() - 1 + 4));//起始行，结束行，起始列，结束列
                    workbook.GetSheetAt(0).AddMergedRegion(new CellRangeAddress(1, 1, 0, exportlist.Count() - 1 + 4));//起始行，结束行，起始列，结束列
                }
                else
                {
                    workbook.GetSheetAt(0).AddMergedRegion(new CellRangeAddress(0, 0, 0, exportlist.Count() - 1));//起始行，结束行，起始列，结束列
                    workbook.GetSheetAt(0).AddMergedRegion(new CellRangeAddress(1, 1, 0, exportlist.Count() - 1));//起始行，结束行，起始列，结束列
                }
            }

            using (FileStream url = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write))
            {
                workbook.Write(url);
            }
        }

        /// <summary>
        /// 从Excel导入数据到DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public List<DataTable> LoadTableFromExcel(string filePath)
        {
            try
            {
                List<DataTable> dataTables = new List<DataTable>();
                string strConn;
                DataSet OleDsExcle = null;
                //Microsoft.Jet.OLEDB.4.0（以下简称 Jet 引擎），可以访问 Office 97-2003（扩展名是mdb），但是在它们以上的版本就不能访问了。
                //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

                //Microsoft.ACE.OLEDB.12.0（以下简称 ACE 引擎），ACE 引擎是随 Office 2007 一起发布 的数据库连接组件，可以访问 Office 97—2003，也可以访问Office 2007以上版本（扩展名是accdb）。
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

                using (OleDbConnection OleConn = new OleDbConnection(strConn))
                {
                    OleConn.Open();
                    DataTable schemaTable = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (null != schemaTable && 0 < schemaTable.Rows.Count)
                    {
                        foreach (DataRow item in schemaTable.Rows)
                        {
                            string strTablename = item[2].ToString();
                            string sql = string.Format("SELECT * FROM [{0}] ", strTablename);
                            using (OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn))
                            {
                                OleDsExcle = new DataSet();
                                OleDaExcel.Fill(OleDsExcle, item[2].ToString());
                                if (null != OleDsExcle)
                                {
                                    dataTables.Add(OleDsExcle.Tables[0]);
                                }
                            }
                        }
                    }
                    OleConn.Close();
                }

                return dataTables;
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 通用数据导出到Excel
        /// </summary>
        /// <param name="tem_path"></param>
        /// <param name="fileName"></param>
        /// <param name="msg"></param>
        /// <param name="action"></param>
        /// <param name="rq"></param>
        /// <returns></returns>
        public static MemoryStream InitExportByDataTableB(string tem_path, string fileName, out string msg,
            Func<HSSFWorkbook, HSSFSheet, HSSFCellStyle, HSSFCellStyle, HSSFCellStyle, HSSFCellStyle, bool> action, bool rq)
        {
            msg = string.Empty;
            HSSFWorkbook hssfworkbookDown;
            tem_path = tem_path.Replace("\\", "/"); // Linux系统部署路径需要改成这样
            using (FileStream file = new FileStream(tem_path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbookDown = new HSSFWorkbook(file);
                file.Close();
            }
            HSSFSheet sheet1 = (HSSFSheet)hssfworkbookDown.GetSheetAt(0);
            sheet1.GetRow(0).Cells[0].SetCellValue(fileName);
            if (rq)
            {
                sheet1.GetRow(1).Cells[0].SetCellValue("日期：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }

            //表头
            IFont font2 = hssfworkbookDown.CreateFont();
            font2.FontHeightInPoints = 16;
            font2.Boldweight = 700;
            HSSFCellStyle cellstyle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            cellstyle.Alignment = HorizontalAlignment.Center;
            cellstyle.VerticalAlignment = VerticalAlignment.Center;
            cellstyle.SetFont(font2);
            HSSFCellStyle nocellStyle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            nocellStyle.Alignment = HorizontalAlignment.Center;
            nocellStyle.VerticalAlignment = VerticalAlignment.Center;
            nocellStyle.BorderRight = BorderStyle.Thin;
            nocellStyle.BorderTop = BorderStyle.Thin;
            nocellStyle.BorderLeft = BorderStyle.Thin;
            nocellStyle.BorderBottom = BorderStyle.Thin;
            IFont font1 = hssfworkbookDown.CreateFont();
            font1.FontHeightInPoints = 10;
            IFont font3 = hssfworkbookDown.CreateFont();
            font3.FontHeightInPoints = 12;
            font3.Boldweight = (short)FontBoldWeight.Bold;

            HSSFCellStyle lastSytle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            lastSytle.SetFont(font3);
            lastSytle.Alignment = HorizontalAlignment.Center;
            lastSytle.VerticalAlignment = VerticalAlignment.Center;
            lastSytle.BorderRight = BorderStyle.Thin;
            lastSytle.BorderTop = BorderStyle.Thin;
            lastSytle.BorderLeft = BorderStyle.Thin;
            lastSytle.BorderBottom = BorderStyle.Thin;

            HSSFCellStyle MersgSytle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            MersgSytle.SetFont(font3);
            MersgSytle.Alignment = HorizontalAlignment.Center;
            MersgSytle.VerticalAlignment = VerticalAlignment.Center;
            MersgSytle.BorderRight = BorderStyle.Thin;
            MersgSytle.BorderTop = BorderStyle.Thin;
            MersgSytle.BorderLeft = BorderStyle.Thin;
            MersgSytle.BorderBottom = BorderStyle.Thin;

            HSSFCellStyle TextSytle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();

            TextSytle.Alignment = HorizontalAlignment.Center;
            TextSytle.VerticalAlignment = VerticalAlignment.Center;
            TextSytle.BorderRight = BorderStyle.Thin;
            TextSytle.BorderTop = BorderStyle.Thin;
            TextSytle.BorderLeft = BorderStyle.Thin;
            TextSytle.BorderBottom = BorderStyle.Thin;

            try
            {
                action.Invoke(hssfworkbookDown, sheet1, nocellStyle, TextSytle, MersgSytle, lastSytle);
                using (MemoryStream ms = new MemoryStream())
                {
                    hssfworkbookDown.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    hssfworkbookDown = null;
                    return ms;
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message + "|" + ex.StackTrace;
                //LogUtil.GetInstance.WriteLogOld("报表导出异常", "当前路径：" + tem_path + "，异常信息为：" + ex.Message, true);
                return null;
            }
        }

        /// <summary>
        /// 用于导出多个sheet 初始化Expor数据
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="excelName"></param>
        /// <param name="action"></param>
        /// <param name="msg"></param>
        /// <param name="srow"></param>
        /// <param name="isrq"></param>
        /// <returns></returns>
        public MemoryStream InitExportDataB(string fileName, string excelName, Action<IRow, ICell, HSSFWorkbook, HSSFSheet, HSSFCellStyle, HSSFCellStyle, int> action, out string msg, int srow, bool isrq = true)
        {
            msg = string.Empty;
            MemoryStream memory = null;
            try
            {
                memory = InitExportByDataTableB(fileName, excelName, out msg, (hssfworkbookDown, sheet1, nocellStyle, TextSytle, MersgSytle, lastSytle) =>
                {
                    IRow row = null;
                    ICell icell = null;
                    action.Invoke(row, icell, hssfworkbookDown, sheet1, TextSytle, lastSytle, srow);
                    return true;
                }, isrq);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return memory;
        }

        /// <summary>
        /// 通用数据导出到Excel(xlsx格式)
        /// </summary>
        /// <param name="tem_path">Excel文件路径</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="date">日期</param>
        /// <param name="dt">数据源</param>
        /// <returns></returns>
        public static MemoryStream InitExportByDataTablexlsx<T>(string tem_path, string fileName, List<T> list, out string msg,
        Func<List<T>, XSSFSheet, XSSFCellStyle, XSSFCellStyle, XSSFCellStyle, XSSFCellStyle, bool> action, bool isdate)
        {
            msg = string.Empty;
            //HSSFWorkbook hssfworkbookDown;
            XSSFWorkbook hssfworkbookDown;
            tem_path = tem_path.Replace("\\", "/"); // Linux系统部署路径需要改成这样
            using (FileStream file = new FileStream(tem_path, FileMode.Open, FileAccess.Read))
            {
                //hssfworkbookDown = new HSSFWorkbook(file);
                hssfworkbookDown = new XSSFWorkbook(file);
                file.Close();
            }
            XSSFSheet sheet1 = (XSSFSheet)hssfworkbookDown.GetSheetAt(0);
            if (isdate)
            {
                sheet1.GetRow(0).Cells[0].SetCellValue(fileName);
                sheet1.GetRow(1).Cells[0].SetCellValue("日期：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }
            //表头
            IFont font2 = hssfworkbookDown.CreateFont();
            font2.FontHeightInPoints = 16;
            font2.Boldweight = 700;

            XSSFCellStyle cellstyle = (XSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            cellstyle.WrapText = true;
            cellstyle.Alignment = HorizontalAlignment.Center;
            cellstyle.VerticalAlignment = VerticalAlignment.Center;
            cellstyle.SetFont(font2);

            XSSFCellStyle nocellStyle = (XSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            nocellStyle.WrapText = true;
            nocellStyle.Alignment = HorizontalAlignment.Center;
            nocellStyle.VerticalAlignment = VerticalAlignment.Center;
            nocellStyle.BorderRight = BorderStyle.Thin;
            nocellStyle.BorderTop = BorderStyle.Thin;
            nocellStyle.BorderLeft = BorderStyle.Thin;
            nocellStyle.BorderBottom = BorderStyle.Thin;
            IFont font1 = hssfworkbookDown.CreateFont();
            font1.FontHeightInPoints = 10;
            IFont font3 = hssfworkbookDown.CreateFont();
            font3.FontHeightInPoints = 12;
            font3.Boldweight = (short)FontBoldWeight.Bold;

            XSSFCellStyle lastSytle = (XSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            lastSytle.WrapText = true;
            lastSytle.SetFont(font3);
            lastSytle.Alignment = HorizontalAlignment.Center;
            lastSytle.VerticalAlignment = VerticalAlignment.Center;
            lastSytle.BorderRight = BorderStyle.Thin;
            lastSytle.BorderTop = BorderStyle.Thin;
            lastSytle.BorderLeft = BorderStyle.Thin;
            lastSytle.BorderBottom = BorderStyle.Thin;

            XSSFCellStyle MersgSytle = (XSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            MersgSytle.SetFont(font3);
            MersgSytle.Alignment = HorizontalAlignment.Center;
            MersgSytle.VerticalAlignment = VerticalAlignment.Center;
            MersgSytle.BorderRight = BorderStyle.Thin;
            MersgSytle.BorderTop = BorderStyle.Thin;
            MersgSytle.BorderLeft = BorderStyle.Thin;
            MersgSytle.BorderBottom = BorderStyle.Thin;

            XSSFCellStyle TextSytle = (XSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            TextSytle.WrapText = true;

            TextSytle.Alignment = HorizontalAlignment.Center;
            TextSytle.VerticalAlignment = VerticalAlignment.Center;
            TextSytle.BorderRight = BorderStyle.Thin;
            TextSytle.BorderTop = BorderStyle.Thin;
            TextSytle.BorderLeft = BorderStyle.Thin;
            TextSytle.BorderBottom = BorderStyle.Thin;

            try
            {
                action.Invoke(list, sheet1, nocellStyle, TextSytle, MersgSytle, lastSytle);
                using (MemoryStream ms = new MemoryStream())
                {
                    hssfworkbookDown.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    hssfworkbookDown = null;
                    return ms;
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message + "|" + ex.StackTrace;
                //LogUtil.GetInstance.WriteLogOld("报表导出异常", "当前路径：" + tem_path + "，异常信息为：" + ex.Message, true);
                return null;
            }
        }

        /// <summary>
        /// 通用数据导出到Excel(xls格式)
        /// </summary>
        /// <param name="tem_path">Excel文件路径</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="date">日期</param>
        /// <param name="dt">数据源</param>
        /// <returns></returns>
        public static MemoryStream InitExportByDataTable<T>(string tem_path, string fileName, List<T> list, out string msg,
        Func<List<T>, HSSFSheet, HSSFCellStyle, HSSFCellStyle, HSSFCellStyle, HSSFCellStyle, HSSFWorkbook, bool> action, bool isdate)
        {
            msg = string.Empty;
            HSSFWorkbook hssfworkbookDown;
            tem_path = tem_path.Replace("\\", "/"); // Linux系统部署路径需要改成这样
            using (FileStream file = new FileStream(tem_path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbookDown = new HSSFWorkbook(file);
                file.Close();
            }
            HSSFSheet sheet1 = (HSSFSheet)hssfworkbookDown.GetSheetAt(0);
            if (isdate)
            {
                sheet1.GetRow(0).Cells[0].SetCellValue(fileName);
                sheet1.GetRow(1).Cells[0].SetCellValue("日期：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }
            //表头
            IFont font2 = hssfworkbookDown.CreateFont();
            font2.FontHeightInPoints = 16;
            font2.Boldweight = 700;

            HSSFCellStyle cellstyle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            cellstyle.WrapText = true;
            cellstyle.Alignment = HorizontalAlignment.Center;
            cellstyle.VerticalAlignment = VerticalAlignment.Center;
            cellstyle.SetFont(font2);

            HSSFCellStyle nocellStyle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            nocellStyle.WrapText = true; //换行
            nocellStyle.Alignment = HorizontalAlignment.Center;
            nocellStyle.VerticalAlignment = VerticalAlignment.Center;
            nocellStyle.BorderRight = BorderStyle.Thin;
            nocellStyle.BorderTop = BorderStyle.Thin;
            nocellStyle.BorderLeft = BorderStyle.Thin;
            nocellStyle.BorderBottom = BorderStyle.Thin;
            IFont font1 = hssfworkbookDown.CreateFont();
            font1.FontHeightInPoints = 10;
            IFont font3 = hssfworkbookDown.CreateFont();
            font3.FontHeightInPoints = 12;
            font3.Boldweight = (short)FontBoldWeight.Bold;

            HSSFCellStyle lastSytle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            lastSytle.WrapText = true; //换行
            lastSytle.SetFont(font3);
            lastSytle.Alignment = HorizontalAlignment.Center;
            lastSytle.VerticalAlignment = VerticalAlignment.Center;
            lastSytle.BorderRight = BorderStyle.Thin;
            lastSytle.BorderTop = BorderStyle.Thin;
            lastSytle.BorderLeft = BorderStyle.Thin;
            lastSytle.BorderBottom = BorderStyle.Thin;

            HSSFCellStyle MersgSytle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            MersgSytle.SetFont(font3);
            MersgSytle.WrapText = true; //换行
            MersgSytle.Alignment = HorizontalAlignment.Center;
            MersgSytle.VerticalAlignment = VerticalAlignment.Center;
            MersgSytle.BorderRight = BorderStyle.Thin;
            MersgSytle.BorderTop = BorderStyle.Thin;
            MersgSytle.BorderLeft = BorderStyle.Thin;
            MersgSytle.BorderBottom = BorderStyle.Thin;

            HSSFCellStyle TextSytle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            TextSytle.WrapText = true; //换行
            TextSytle.Alignment = HorizontalAlignment.Center;
            TextSytle.VerticalAlignment = VerticalAlignment.Center;
            TextSytle.BorderRight = BorderStyle.Thin;
            TextSytle.BorderTop = BorderStyle.Thin;
            TextSytle.BorderLeft = BorderStyle.Thin;
            TextSytle.BorderBottom = BorderStyle.Thin;

            try
            {
                action.Invoke(list, sheet1, nocellStyle, TextSytle, MersgSytle, lastSytle, hssfworkbookDown);
                MemoryStream ms = new MemoryStream();
                hssfworkbookDown.Write(ms);
                ms.Flush();
                ms.Position = 0;
                hssfworkbookDown = null;
                return ms;
                //using (MemoryStream ms = new MemoryStream())
                //{
                //    hssfworkbookDown.Write(ms);
                //    ms.Flush();
                //    ms.Position = 0;
                //    hssfworkbookDown = null;
                //    return ms;
                //}
            }
            catch (Exception ex)
            {
                msg = ex.Message + "|" + ex.StackTrace;
                //LogUtil.GetInstance.WriteLogOld("报表导出异常", "当前路径：" + tem_path + "，异常信息为：" + ex.Message, true);
                return null;
            }
        }

        public static byte[] InitExportByDataTableToByte<T>(string tem_path, string fileName, List<T> list, out string msg,
               Func<List<T>, HSSFSheet, HSSFCellStyle, HSSFCellStyle, HSSFCellStyle, HSSFCellStyle, bool> action)
        {
            msg = string.Empty;
            HSSFWorkbook hssfworkbookDown;
            using (FileStream file = new FileStream(tem_path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbookDown = new HSSFWorkbook(file);
                file.Close();
            }
            HSSFSheet sheet1 = (HSSFSheet)hssfworkbookDown.GetSheetAt(0);
            sheet1.GetRow(0).Cells[0].SetCellValue(fileName);
            sheet1.GetRow(1).Cells[0].SetCellValue("日期：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            //表头
            IFont font2 = hssfworkbookDown.CreateFont();
            font2.FontHeightInPoints = 16;
            font2.Boldweight = 700;

            HSSFCellStyle cellstyle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            cellstyle.WrapText = true;
            cellstyle.Alignment = HorizontalAlignment.Center;
            cellstyle.VerticalAlignment = VerticalAlignment.Center;
            cellstyle.SetFont(font2);

            HSSFCellStyle nocellStyle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            nocellStyle.WrapText = true;
            nocellStyle.Alignment = HorizontalAlignment.Center;
            nocellStyle.VerticalAlignment = VerticalAlignment.Center;
            nocellStyle.BorderRight = BorderStyle.Thin;
            nocellStyle.BorderTop = BorderStyle.Thin;
            nocellStyle.BorderLeft = BorderStyle.Thin;
            nocellStyle.BorderBottom = BorderStyle.Thin;
            IFont font1 = hssfworkbookDown.CreateFont();
            font1.FontHeightInPoints = 10;
            IFont font3 = hssfworkbookDown.CreateFont();
            font3.FontHeightInPoints = 12;
            font3.Boldweight = (short)FontBoldWeight.Bold;

            HSSFCellStyle lastSytle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            lastSytle.WrapText = true;
            lastSytle.SetFont(font3);
            lastSytle.Alignment = HorizontalAlignment.Center;
            lastSytle.VerticalAlignment = VerticalAlignment.Center;
            lastSytle.BorderRight = BorderStyle.Thin;
            lastSytle.BorderTop = BorderStyle.Thin;
            lastSytle.BorderLeft = BorderStyle.Thin;
            lastSytle.BorderBottom = BorderStyle.Thin;

            HSSFCellStyle MersgSytle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            MersgSytle.SetFont(font3);
            MersgSytle.Alignment = HorizontalAlignment.Center;
            MersgSytle.VerticalAlignment = VerticalAlignment.Center;
            MersgSytle.BorderRight = BorderStyle.Thin;
            MersgSytle.BorderTop = BorderStyle.Thin;
            MersgSytle.BorderLeft = BorderStyle.Thin;
            MersgSytle.BorderBottom = BorderStyle.Thin;

            HSSFCellStyle TextSytle = (HSSFCellStyle)hssfworkbookDown.CreateCellStyle();
            TextSytle.WrapText = true;

            TextSytle.Alignment = HorizontalAlignment.Center;
            TextSytle.VerticalAlignment = VerticalAlignment.Center;
            TextSytle.BorderRight = BorderStyle.Thin;
            TextSytle.BorderTop = BorderStyle.Thin;
            TextSytle.BorderLeft = BorderStyle.Thin;
            TextSytle.BorderBottom = BorderStyle.Thin;

            try
            {
                action.Invoke(list, sheet1, nocellStyle, TextSytle, MersgSytle, lastSytle);

                byte[] buffer = new byte[1024 * 5];
                using (MemoryStream ms = new MemoryStream())
                {
                    hssfworkbookDown.Write(ms);
                    buffer = ms.GetBuffer();
                    ms.Close();
                }
                return buffer;
            }
            catch (Exception ex)
            {
                msg = ex.Message + "|" + ex.StackTrace;
                return null;
            }
        }

        /// <summary>
        ///用于导出单个sheet 初始化Expor数据
        /// </summary>
        /// <param name="excelName"></param>
        /// <param name="list"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public MemoryStream InitExportData<T>(string fileName, string excelName, List<T> list, Action<IRow, ICell, HSSFSheet, HSSFCellStyle, int, HSSFWorkbook> action, out string msg, int srow, bool isdate = true)
        {
            msg = string.Empty;
            MemoryStream memory = null;
            if (list != null)
            {
                try
                {
                    memory = InitExportByDataTable(fileName, excelName, list, out msg, (data, sheet1, nocellStyle, TextSytle, MersgSytle, lastSytle, hssfworkbook) =>
                    {
                        IRow row = null;
                        ICell icell = null;
                        action.Invoke(row, icell, sheet1, TextSytle, srow, hssfworkbook);
                        return true;
                    }, isdate);
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                }
                return memory;
            }
            else
                return memory;
        }

        /// <summary>
        ///用于导出单个sheet 初始化Expor数据
        /// </summary>
        /// <param name="excelName"></param>
        /// <param name="list"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public MemoryStream InitExportDataxlsx<T>(string fileName, string excelName, List<T> list, Action<IRow, ICell, XSSFSheet, XSSFCellStyle, int> action, out string msg, int srow, bool isdate = true)
        {
            msg = string.Empty;
            MemoryStream memory = null;
            if (list != null && list.Count > 0)
            {
                try
                {
                    memory = InitExportByDataTablexlsx(fileName, excelName, list, out msg, (data, sheet1, nocellStyle, TextSytle, MersgSytle, lastSytle) =>
                    {
                        IRow row = null;
                        ICell icell = null;
                        action.Invoke(row, icell, sheet1, TextSytle, srow);
                        return true;
                    }, isdate);
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                }
                return memory;
            }
            else
                return memory;
        }

        public void WriteExport(string filePath, Action<ISheet> action, out string msg)
        {
            msg = string.Empty;
            try
            {
                IWorkbook workbook = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    workbook = new HSSFWorkbook(fs);
                    fs.Close();
                }

                using (FileStream fileStream = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    for (int i = 0; i < workbook.NumberOfSheets; i++)
                    {
                        ISheet sheet = workbook.GetSheetAt(i);
                        action.Invoke(sheet);
                        workbook.Write(fileStream);
                        fileStream.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }

        }

        /// <summary>
        /// tabel导出excel
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static string getWrapper(string sb,string sheetname)
        {
            StringBuilder str = new StringBuilder();
            str.Append("<html xmlns:o=\"urn:schemas-microsoft-com:office:excel\"");
            //str.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
            str.Append("xmlns=\"http://www.w3.org/TR/REC-html40\"");
            str.Append("<head> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'>");
            str.Append("<xml>");
            str.Append("<x:ExcelWorkbook>");
            str.Append("<x:ExcelWorksheets>");
            str.Append("<x:ExcelWorksheet>");
            str.Append("<x:Name>"+ sheetname + "</x:Name>");
            str.Append("<x:WorksheetOptions>");
            str.Append("<x:Print/>");
            //str.Append("<x:DefaultRowHeight>285 </x:DefaultRowHeight>");
            //str.Append("<x:DefaultRowWidth>385 </x:DefaultRowHeight>");
            //str.Append("<x:Selected/>");
            //str.Append("<x:Panes>");
            //str.Append("<x:Pane>");
            //str.Append("<x:Number>0 </x:Number>");
            //str.Append("<x:ActiveCol>0 </x:ActiveCol>");
            //str.Append("</x:Pane>");
            //str.Append("</x:Panes>");
            //str.Append("<x:ProtectContents>False </x:ProtectContents>");
            //str.Append("<x:ProtectObjects>False </x:ProtectObjects>");
            //str.Append("<x:ProtectScenarios>False </x:ProtectScenarios>");
            str.Append("</x:WorksheetOptions>");
            str.Append("</x:ExcelWorksheet>");
            //str.Append("<x:WindowHeight>6750 </x:WindowHeight>");
            //str.Append("<x:WindowWidth>10620 </x:WindowWidth>");
            //str.Append("<x:WindowTopX>480 </x:WindowTopX>");
            //str.Append("<x:WindowTopY>75 </x:WindowTopY>");
            //str.Append("<x:ProtectStructure>False </x:ProtectStructure>");
            //str.Append("<x:ProtectWindows>False </x:ProtectWindows>");
            str.Append("</x:ExcelWorkbook>");
            str.Append("</xml>");
            str.Append("</head><body> ");
            str.Append(sb);
            str.Append("</body></html>");
            return str.ToString();
        }
    }

    /// <summary>
    /// Excel样式设置
    /// </summary>
    public static class ExcelStyle
    {
        /// <summary>
        /// 加粗字体
        /// </summary>
        /// <param name="stle"></param>
        /// <param name="hssfworkbookDown"></param>
        /// <returns></returns>
        public static ICellStyle SetCreateCellStyle(this ICellStyle stle, HSSFWorkbook hssfworkbookDown)
        {
            IFont font = hssfworkbookDown.CreateFont();
            font.FontHeightInPoints = 12;
            font.Boldweight = (short)FontBoldWeight.Bold;
            stle.SetFont(font);
            return stle;
        }
    }
}
